package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.HashMap;

import com.telecom.cms.dto.TwoId;
import com.telecom.cms.dto.CampCustAppdRslt;
import com.telecom.cms.util.Constant;

public class CampCustAppdRsltDAO {

	Dao dao = new DaoImp();

	String db = (new Constant()).getPara("DATABASE");

	public int insert(CampCustAppdRslt transientInstance) {
		int rs = 0;
		String sql = "select * from  " + db
				+ ".Camp_Cust_Appd_Rslt where seq_id="
				+ transientInstance.getSeqId();
		try {
			ResultSet rs2 = dao.select(sql);
			if (rs2.next())
				sql = "update " + db + ".Camp_Cust_Appd_Rslt set seq_id='"
						+ transientInstance.getSeqId() + "',latn_id='"
						+ transientInstance.getLatnId() + "',cust_id='"
						+ transientInstance.getCustId() + "',prd_inst_id='"
						+ transientInstance.getPrdInstId() + "',accs_nbr='"
						+ transientInstance.getAccsNbr() + "',all_accs_nbr='"
						+ transientInstance.getAllAccsNbr() + "',clu_type='"
						+ transientInstance.getCluType()
						+ "',camp_id='" + transientInstance.getCampId()
						+ "',camp_seq_id='" + transientInstance.getCampSeqId()

						+ "',user_id='" + transientInstance.getUserId()
						+ " where seq_id=" + transientInstance.getSeqId();
			else
				sql = "insert into " + db + ".Camp_Cust_Appd_Rslt values('"
						+ transientInstance.getSeqId() + "' , '"
						+ transientInstance.getLatnId() + "' , '"
						+ transientInstance.getCustId() + "' , '"
						+ transientInstance.getPrdInstId() + "' , '"
						+ transientInstance.getAccsNbr() + "' , '"
						+ transientInstance.getAllAccsNbr() + "' , '"
						+ transientInstance.getCluType() + "' , '"
						+ transientInstance.getCampId() + "' , '"
						+ transientInstance.getCampSeqId() + "' , '"
						+ transientInstance.getUserId() + "')";
			rs = dao.insert(sql);
			
			if (rs2 != null) {

				rs2.close();
			}

		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}
		finally
		{
			dao.closeconn();
		}
		return rs;

	}

	public void delete(CampCustAppdRslt persistentInstance) {

		String sql = "delete from " + db
				+ ".Camp_Cust_Appd_Rslt where Seq_Id = "
				+ persistentInstance.getSeqId();

		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public CampCustAppdRslt findById(String id) {

		String sql = "select * from " + db
				+ ".Camp_Cust_Appd_Rslt where Camp_Seq_Id = " + id;
		CampCustAppdRslt sd = new CampCustAppdRslt();
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {
				sd.setSeqId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setCustId(rs.getString(3));
				sd.setPrdInstId(rs.getString(4));
				sd.setAccsNbr(rs.getString(5));
				sd.setAllAccsNbr(rs.getString(6));
				sd.setCluType(rs.getString(7));
				;
				sd.setCampId(rs.getInt(8));
				sd.setCampSeqId(rs.getInt(9));
				sd.setUserId(rs.getInt(10));
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return sd;

	}

	public List findAll() {

		String sql = "select * from " + db + ".Camp_Cust_Appd_Rslt";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampCustAppdRslt sd = new CampCustAppdRslt();
				sd.setSeqId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setCustId(rs.getString(3));
				sd.setPrdInstId(rs.getString(4));
				sd.setAccsNbr(rs.getString(5));
				sd.setAllAccsNbr(rs.getString(6));
				sd.setCluType(rs.getString(7));
				;
				sd.setCampId(rs.getInt(8));
				sd.setCampSeqId(rs.getInt(9));
				sd.setUserId(rs.getInt(10));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;

	}
	
	public List findAll(String campid,String campseqid,String userid,String custtype) {

		String sql = null;
		
		if(custtype.equals("nocomm"))  //find all users no touch
			sql="select * from " + db + ".Camp_Cust_Appd_Rslt a inner join "
                +" (select cust_id,prd_inst_id from " + db + ".Camp_Cust_Appd_Rslt " 
                		+ " where camp_id="+campid+" and camp_seq_id="+campseqid+" and user_id="+userid
                		+ " minus select cust_id,prd_inst_id from " + db + ".camp_cont_rslt "
                        + " where  camp_id="+campid+" and camp_seq_id="+campseqid+" group by 1,2) as b "
                        + " on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
                        + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                        + " order by 3,4";
		else if(custtype.equals("notouch"))  //find all users no comm
			sql="select * from " + db + ".Camp_Cust_Appd_Rslt a inner join "
            +" (select cust_id,prd_inst_id from " + db + ".Camp_Cust_Appd_Rslt " 
            		+ " where camp_id="+campid+" and camp_seq_id="+campseqid+" and user_id="+userid
            		+ " minus select cust_id,prd_inst_id from " + db + ".camp_cont_rslt "
                    + " where  camp_id="+campid+" and camp_seq_id="+campseqid+" and cast(Camp_Cont_Rslt_Lvl_Id as char(2))>'10') as b "
                    + " on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
                    + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                    + " order by 3,4";
		else if(custtype.equals("appoint")) //find all users appointed
			sql="select * from " + db + ".Camp_Cust_Appd_Rslt a inner join "
                +" (select * from (select cust_id,prd_inst_id,appoint_time from " + db + ".Camp_Cont_Rslt " 
                		+ " where camp_id="+campid+" and camp_seq_id="+campseqid
                        + " QUALIFY ROW_NUMBER() over (PARTITION BY cust_id,prd_inst_id ORDER BY cont_seq_id desc ) = 1 ) kkb" 
                        + " where kkb.appoint_time is not null  "
                		+ ") as b on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
                        + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                        + " order by b.appoint_time ";
		else if(custtype.equals("appointMorning"))
			sql="select * from " + db + ".Camp_Cust_Appd_Rslt a inner join "
                +" (select * from (select cust_id,prd_inst_id,appoint_time from " + db + ".Camp_Cont_Rslt " 
                		+ " where camp_id="+campid+" and camp_seq_id="+campseqid
                        + " QUALIFY ROW_NUMBER() over (PARTITION BY cust_id,prd_inst_id ORDER BY cont_seq_id desc ) = 1 ) kkb" 
                        + " where kkb.appoint_time is not null and extract(hour from kkb.appoint_time) < 12 "
                		+ ") as b on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
                        + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                        + " order by b.appoint_time ";
		else if(custtype.equals("appointAfternoon")) 
			sql="select * from " + db + ".Camp_Cust_Appd_Rslt a inner join "
                +" (select * from (select cust_id,prd_inst_id,appoint_time from " + db + ".Camp_Cont_Rslt " 
                		+ " where camp_id="+campid+" and camp_seq_id="+campseqid
                        + " QUALIFY ROW_NUMBER() over (PARTITION BY cust_id,prd_inst_id ORDER BY cont_seq_id desc ) = 1 ) kkb" 
                        + " where kkb.appoint_time is not null and extract(hour from kkb.appoint_time) >= 12 and extract(hour from kkb.appoint_time) < 18 "
                		+ ") as b on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
                        + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                        + " order by b.appoint_time ";
		else if(custtype.equals("appointNight")) 
			sql="select * from " + db + ".Camp_Cust_Appd_Rslt a inner join "
                +" (select * from (select cust_id,prd_inst_id,appoint_time from " + db + ".Camp_Cont_Rslt " 
                		+ " where camp_id="+campid+" and camp_seq_id="+campseqid
                        + " QUALIFY ROW_NUMBER() over (PARTITION BY cust_id,prd_inst_id ORDER BY cont_seq_id desc ) = 1 ) kkb" 
                        + " where kkb.appoint_time is not null and extract(hour from kkb.appoint_time) > 18 "
                		+ ") as b on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
                        + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                        + " order by b.appoint_time ";
		else if(custtype.equals("morning"))  //find all users recommend morning call
			sql="select a.*,coalesce(c.cont_seq_id,0) from " + db + ".Camp_Cust_Appd_Rslt a inner join "
            +" (select cust_id,prd_inst_id from " + db + ".CAMP_CUST_ADV_INFO " 
            		+ " where camp_id="+campid+" and camp_seq_id="+campseqid+" and comm_info like '%上午%') as b "
                    + " on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id " 
                    + " left join "+ db + ".camp_cont_rslt c" 
                    + " on a.camp_id=c.camp_id and a.camp_seq_id=c.camp_seq_id and a.cust_id=c.cust_id and a.prd_inst_id=c.prd_inst_id" 
                    + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                    + " order by coalesce(c.cont_seq_id,0),3,4";
		else if(custtype.equals("afternoon"))  //find all users recommend afternoon call
			sql="select a.*,coalesce(c.cont_seq_id,0) from " + db + ".Camp_Cust_Appd_Rslt a inner join "
            +" (select cust_id,prd_inst_id from " + db + ".CAMP_CUST_ADV_INFO " 
            		+ " where camp_id="+campid+" and camp_seq_id="+campseqid+" and comm_info like '%下午%') as b "
                    + " on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
                    + " left join "+ db + ".camp_cont_rslt c" 
                    + " on a.camp_id=c.camp_id and a.camp_seq_id=c.camp_seq_id and a.cust_id=c.cust_id and a.prd_inst_id=c.prd_inst_id" 
                    + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                    + " order by coalesce(c.cont_seq_id,0),3,4";
		else if(custtype.equals("night"))  //find all users recommend night call
			sql="select a.*,coalesce(c.cont_seq_id,0) from " + db + ".Camp_Cust_Appd_Rslt a inner join "
            +" (select cust_id,prd_inst_id from " + db + ".CAMP_CUST_ADV_INFO " 
            		+ " where camp_id="+campid+" and camp_seq_id="+campseqid+" and comm_info like '%晚上%') as b "
                    + " on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
                    + " left join "+ db + ".camp_cont_rslt c" 
                    + " on a.camp_id=c.camp_id and a.camp_seq_id=c.camp_seq_id and a.cust_id=c.cust_id and a.prd_inst_id=c.prd_inst_id" 
                    + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                    + " order by coalesce(c.cont_seq_id,0),3,4";
		else if(custtype.equals("notouchmorn"))  //find notouch users recommend morning call
				sql="select * from " + db + ".Camp_Cust_Appd_Rslt a inner join "
	            +" (select cust_id,prd_inst_id from " + db + ".CAMP_CUST_ADV_INFO " 
	            		+ " where camp_id="+campid+" and camp_seq_id="+campseqid+" and comm_info like '%上午%'"
	            		+ " minus select cust_id,prd_inst_id from " + db + ".camp_cont_rslt "
                        + " where  camp_id="+campid+" and camp_seq_id="+campseqid+" and cast(Camp_Cont_Rslt_Lvl_Id as char(2))>'10') as b "
	                    + " on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
	                    + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
	                    + " order by 3,4";
		else if(custtype.equals("notouchafter"))  //find notouch users recommend afternoon call
			sql="select * from " + db + ".Camp_Cust_Appd_Rslt a inner join "
            +" (select cust_id,prd_inst_id from " + db + ".CAMP_CUST_ADV_INFO " 
            		+ " where camp_id="+campid+" and camp_seq_id="+campseqid+" and comm_info like '%下午%'"
            		+ " minus select cust_id,prd_inst_id from " + db + ".camp_cont_rslt "
                    + " where  camp_id="+campid+" and camp_seq_id="+campseqid+" and cast(Camp_Cont_Rslt_Lvl_Id as char(2))>'10') as b "
                    + " on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
                    + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                    + " order by 3,4";
		else if(custtype.equals("notouchnight"))  //find notouch users recommend night call
			sql="select * from " + db + ".Camp_Cust_Appd_Rslt a inner join "
            +" (select cust_id,prd_inst_id from " + db + ".CAMP_CUST_ADV_INFO " 
            		+ " where camp_id="+campid+" and camp_seq_id="+campseqid+" and comm_info like '%晚上%'"
            		+ " minus select cust_id,prd_inst_id from " + db + ".camp_cont_rslt "
                    + " where  camp_id="+campid+" and camp_seq_id="+campseqid+" and cast(Camp_Cont_Rslt_Lvl_Id as char(2))>'10') as b "
                    + " on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
                    + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                    + " order by 3,4";
		else if(custtype.equals("nocommMorning"))  
			sql="select * from " + db + ".Camp_Cust_Appd_Rslt a inner join "
            +" (select cust_id,prd_inst_id from " + db + ".CAMP_CUST_ADV_INFO " 
            		+ " where camp_id="+campid+" and camp_seq_id="+campseqid+" and comm_info like '%上午%'"
            		+ " minus select cust_id,prd_inst_id from " + db + ".camp_cont_rslt "
                    + " where  camp_id="+campid+" and camp_seq_id="+campseqid+" group by 1,2) as b "
                    + " on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
                    + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                    + " order by 3,4";
		else if(custtype.equals("nocommAfternoon"))  
			sql="select * from " + db + ".Camp_Cust_Appd_Rslt a inner join "
            +" (select cust_id,prd_inst_id from " + db + ".CAMP_CUST_ADV_INFO " 
            		+ " where camp_id="+campid+" and camp_seq_id="+campseqid+" and comm_info like '%下午%'"
            		+ " minus select cust_id,prd_inst_id from " + db + ".camp_cont_rslt "
                    + " where  camp_id="+campid+" and camp_seq_id="+campseqid+" group by 1,2) as b "
                    + " on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
                    + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                    + " order by 3,4";
		else if(custtype.equals("nocommNight"))  
			sql="select * from " + db + ".Camp_Cust_Appd_Rslt a inner join "
            +" (select cust_id,prd_inst_id from " + db + ".CAMP_CUST_ADV_INFO " 
            		+ " where camp_id="+campid+" and camp_seq_id="+campseqid+" and comm_info like '%晚上%'"
            		+ " minus select cust_id,prd_inst_id from " + db + ".camp_cont_rslt "
                    + " where  camp_id="+campid+" and camp_seq_id="+campseqid+" group by 1,2) as b "
                    + " on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
                    + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                    + " order by 3,4";
		else if(custtype.equals("all")) //find all user
		{
			sql= "select * from " + db + ".Camp_Cust_Appd_Rslt where camp_id="
		        +campid+" and camp_seq_id="+campseqid+" and user_id="+userid
		        + " order by 3,4";
		    sql= "select a.*,coalesce(c.cont_seq_id,0) from " + db + ".Camp_Cust_Appd_Rslt a "
                + " left join "+ db + ".camp_cont_rslt c" 
                + " on a.camp_id=c.camp_id and a.camp_seq_id=c.camp_seq_id and a.cust_id=c.cust_id and a.prd_inst_id=c.prd_inst_id" 
                + " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id="+userid
                + " order by coalesce(c.cont_seq_id,0),3,4";
		}
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				TwoId sd = new TwoId();
				/*sd.setSeqId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setCustId(rs.getString(3));
				sd.setPrdInstId(rs.getString(4));
				sd.setAccsNbr(rs.getString(5));
				sd.setAllAccsNbr(rs.getString(6));
				sd.setCluType(rs.getString(7));
				sd.setCampId(rs.getInt(8));
				sd.setCampSeqId(rs.getInt(9));
				sd.setUserId(rs.getInt(10));*/
				sd.setCustId(rs.getString(3));
				sd.setPrdInstId(rs.getString(4));
				sd.setAccsnbr(rs.getString(5));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;

	}
	
	public HashMap lastappointuser(String campid,String campseqid,String userid,String username) {
		String sql = null;
		
		sql=sql="select a.cust_id,a.prd_inst_id,a.accs_nbr,b.appoint_time from " + db + ".Camp_Cust_Appd_Rslt a inner join "
			+" (select * from (select cust_id,prd_inst_id,appoint_time from " + db + ".Camp_Cont_Rslt " 
			+ " where camp_id="+campid+" and camp_seq_id="+campseqid+" and user_id='"+username
			+ "' QUALIFY ROW_NUMBER() over (PARTITION BY cust_id,prd_inst_id ORDER BY cont_seq_id desc ) = 1 ) kkb" 
			+ " where kkb.appoint_time is not null and  (appoint_time-cast(current_time as timestamp(0))) DAY(3) TO MINUTE < Interval '000 15:00' DAY TO MINUTE "
			+ ") as b on a.cust_id=b.cust_id and a.prd_inst_id=b.prd_inst_id "
			+ " where a.camp_id="+campid+" and a.camp_seq_id="+campseqid+" and a.user_id='"+userid+"'";
		
		HashMap list = new HashMap();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				TwoId sd = new TwoId();
				sd.setCustId(rs.getString(1));
				sd.setPrdInstId(rs.getString(2));
				//sd.setAccsnbr(rs.getString(3));
				list.put(sd,rs.getString(4));
			}
			if (rs != null) {
				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;
	}

	public void appdusernew(String campid,String campseqid,String appdnum,String userid) {

		String sql = null;
		
		try {
			sql="delete from  " + db + ".CAMP_CUST_APPD_RSLT_USE where camp_id="+campid
			    +" and camp_seq_id="+campseqid+" and user_id="+userid;
			dao.insert(sql);
			
			sql="update " + db + ".CAMP_CUST_APPD_RSLT set user_id=0 where camp_id="+campid
		    +" and camp_seq_id="+campseqid+" and user_id="+userid;
		    dao.insert(sql);
		    
		    sql="insert " + db + ".CAMP_CUST_APPD_RSLT_USE(seq_id,camp_id,camp_seq_id,user_id) select seq_id,camp_id,camp_seq_id,"
		            +userid+" from "+ db + ".CAMP_CUST_APPD_RSLT where camp_id="+campid
		            +" and camp_seq_id="+campseqid+"  and (user_id is null or user_id=0) sample "+appdnum;
		    dao.insert(sql);
		    
		    sql="update " + db + ".CAMP_CUST_APPD_RSLT set user_id="+userid
		    	+" where seq_id in(select seq_id from "
                + db + ".CAMP_CUST_APPD_RSLT_USE where camp_id="
                +campid+" and camp_seq_id="+campseqid+" and user_id="+userid+")";
            dao.insert(sql);
            
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
